package services;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Vector;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/*
 * Product Family	0
 * Course Name	1
 * Environment	2
 * Site	
 * Virtual Pods, Mobile Kits, Physical Classrooms	
 * Max Students per Class (ILT/VA)	# Systems per Student (ILT/VA)	
 * Audience	
 * Saba Course ID [SES Portal]	SymLearn Course ID [TTE Portal]	Gilmore ID	LAB INFO - Lab Image	Notes	Delivery Infrastructure Notes																							*/
public class LoadCourseMatrix implements Runnable{
	
	private String WorkBookName;
	public String outputMsg;
	private FileInputStream fis;
	private FileOutputStream fos;
	private XSSFWorkbook wb;
	private XSSFSheet currentSheet;
	private XSSFRow currentRow;
	private XSSFCell currentCell;
	private Vector<Vector> sheetData;
	public Vector<Vector> VAClassesData;
	public Vector<String> CourseName;
	
	public LoadCourseMatrix(){
		WorkBookName = "";
		outputMsg = "";
		sheetData = new Vector<Vector>();
		VAClassesData = new Vector<Vector>();
		CourseName = new Vector<String>();
	}
	
	public void setWorkbookName(String fileName)
	{
		WorkBookName = fileName;
	}
		
	private boolean loadVAClasses()
	{
		int iTest = 0, iTCell, currentLine;
		
		try {
			fis = new FileInputStream(WorkBookName);
		    wb = new XSSFWorkbook(fis);
		    			
			if (currentSheet != null)
			{
				currentSheet = null;
			}		
			currentSheet = wb.getSheet("Data Base");
			outputMsg = outputMsg.concat("Sheet \"Data Base\" Activated. \nLoading ...");
			System.out.print("Sheet \"Data Base\" Activated. \n Loading");
			
			iTest = 0;
			
			if (!sheetData.isEmpty())
			{
				sheetData.clear();
			}
			
			while (iTest < currentSheet.getPhysicalNumberOfRows())
			{
				currentRow = currentSheet.getRow(iTest);
				sheetData.add(new Vector());
				iTCell = 0;
				while (iTCell < currentRow.getPhysicalNumberOfCells())
				{
						currentCell = currentRow.getCell(iTCell);
						sheetData.elementAt(iTest).add(currentCell);
						iTCell ++;
				}			
				iTest ++;
			}
			outputMsg = outputMsg.concat("\nLoaded " + String.valueOf(iTest) + " rows data. \n");
			System.out.println("\nLoaded " + iTest + " rows data.");
			fis.close();
		} catch (IOException e) {
			e.printStackTrace();
			return false;
		} catch (Throwable e) {
			e.printStackTrace();
			return false;
		}

		VAClassesData = new Vector<Vector>();
		
		for (currentLine = 5; currentLine < sheetData.size(); currentLine ++)
		{
			if (sheetData.elementAt(currentLine).size() > 2)
			{
				
				if (sheetData.elementAt(currentLine).elementAt(2).toString().compareTo("Hatsize") == 0)
				{
					VAClassesData.add(sheetData.elementAt(currentLine));
				}
			}
		}
		outputMsg = outputMsg.concat("Found " + String.valueOf(VAClassesData.size()) + " rows of VA class data. \n");
		System.out.println("Found " + VAClassesData.size() + " rows of VA class data.");
		
		for (iTest=0; iTest<VAClassesData.size(); iTest++)
    	{
    		CourseName.addElement(VAClassesData.elementAt(iTest).elementAt(1).toString());
    	}

		
		return true;
}
	
	private void WriteToSheet(){
		int oviRow = 0, oviCol=0;
		String workbookname = "PBDataSheet.xlsx";
		XSSFSheet ovSheet;
		XSSFWorkbook wbs = new XSSFWorkbook();
		
		File fData = new File(workbookname);
		if (fData.exists()){
			fData.delete();
		}
		
		try{
			wbs.createSheet("CourseMatrix");
			wbs.removeSheetAt(wbs.getSheetIndex("CourseMatrix"));
			System.out.println("Old data deleted.");
		}
		catch (Throwable e)
		{
			System.out.println("No sheet \"CourseMatrix\" found, will create a new one.");
		}
		ovSheet = wbs.createSheet("CourseMatrix");
		System.out.print("Proceding rows ");
		for (oviRow=0; oviRow<VAClassesData.size(); oviRow++)
		{
			XSSFRow ovRow = ovSheet.createRow(oviRow);
			for (oviCol=0; oviCol<VAClassesData.elementAt(oviRow).size(); oviCol++){
				XSSFCell ovCell = ovRow.createCell(oviCol);
				if (VAClassesData.elementAt(oviRow).elementAt(oviCol) != null){
					switch (((XSSFCell)VAClassesData.elementAt(oviRow).elementAt(oviCol)).getCellType()){
						case Cell.CELL_TYPE_BLANK:
							ovCell.setCellValue(((XSSFCell)VAClassesData.elementAt(oviRow).elementAt(oviCol)).getRawValue());
							break;
						case Cell.CELL_TYPE_BOOLEAN:
							ovCell.setCellValue(((XSSFCell)VAClassesData.elementAt(oviRow).elementAt(oviCol)).getBooleanCellValue());
							break;
						case Cell.CELL_TYPE_ERROR:
							ovCell.setCellValue("");
							break;
						case Cell.CELL_TYPE_FORMULA:
							ovCell.setCellValue(((XSSFCell)VAClassesData.elementAt(oviRow).elementAt(oviCol)).getCellFormula());
							break;
						case Cell.CELL_TYPE_STRING:
							ovCell.setCellValue(((XSSFCell)VAClassesData.elementAt(oviRow).elementAt(oviCol)).getStringCellValue());
							break;
						case Cell.CELL_TYPE_NUMERIC:
							ovCell.setCellValue(((XSSFCell)VAClassesData.elementAt(oviRow).elementAt(oviCol)).getNumericCellValue());
							break;
						default:
							break;
					}
				}
				else
				{
					ovCell.setCellValue("");
				}
			}
		}
		//System.out.println();
		outputMsg = outputMsg.concat("Saving ...\n");
		//System.out.print("\n Saving");
		for (oviCol=0 ; oviCol<VAClassesData.elementAt(0).size(); oviCol++)
		{			
			ovSheet.autoSizeColumn(oviCol);
		}
		
		try {
			FileOutputStream pfos = new FileOutputStream(workbookname);
			wbs.write(pfos);
			outputMsg = outputMsg.concat("Finished successfully. \n");
			System.out.println("done");
			pfos.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	    
}

	@Override
	public void run() {
		if (loadVAClasses())
		{
			if (VAClassesData != null)
			{
				WriteToSheet();
				DataCache.vCourseCache = VAClassesData;
				DataCache.vCourseName.addAll(CourseName);	
			}

		}
		else
		{
			System.out.println("Failed");
		}
		
	}
	
}
